use log::debug;
use rusqlite::{params, ToSql};
use tauri::State;

use crate::error::error::MyError;
use crate::model::company::*;
use crate::model::db_connection::DatabaseConnection;
use crate::model::output::Output;
use crate::model::pagination::Pagination;

#[tauri::command(rename_all = "snake_case")]
pub fn create_company(
    data: &str,
    state: State<DatabaseConnection>,
) -> Result<Output<Company>, MyError> {
    let company: Company = serde_json::from_str(data).map_err(|e| {
        MyError::new(
            1000,
            "Parse JSON error when create company".to_string(),
            e.to_string(),
        )
    })?;

    debug!("Create company: {:?}", company);
    state
        .conn
        .lock()
        .unwrap()
        .execute(
            r#"INSERT INTO company (
        name, type_id, status, address, staff_num, capacity, remark,
        created_by,  modified_by)
        VALUES
        (
        ?,?,?,?,?,?,?,?,?
        )"#,
            params![
                company.name,
                company.type_id,
                company.status,
                company.address,
                company.staff_num,
                company.capacity,
                company.remark,
                "System",
                "System"
            ],
        )
        .map_err(|e| MyError::new(1002, "Insert company error".to_string(), e.to_string()))?;

    return Ok(Output::success("Ok".to_string(), None));
}

#[tauri::command(rename_all = "snake_case")]
pub fn update_company(
    data: &str,
    state: State<DatabaseConnection>,
) -> Result<Output<Company>, MyError> {
    debug!("Update company data: {}", data);
    let company: Company = serde_json::from_str(data).map_err(|e| {
        MyError::new(
            1000,
            "Parse JSON error when update company".to_string(),
            e.to_string(),
        )
    })?;

    // 检查表是否存在
    let conn = state.conn.lock().unwrap();
    let table_exists: bool = conn
        .query_row(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='company'",
            [],
            |row| Ok(row.get::<_, i32>(0)? == 1),
        )
        .map_err(|e| {
            MyError::database_error("Check table existence failed".to_string(), e.to_string())
        })?;

    if !table_exists {
        return Err(MyError::new(
            404,
            "Table 'company' does not exist".to_string(),
            "".to_string(),
        ));
    }

    // 原有的更新逻辑
    let sql = r#"
        UPDATE company
        SET
            name = ?1,
            type_id = ?2,
            status = ?3,
            address = ?4,
            staff_num = ?5,
            remark = ?6,
            capacity = ?7
        WHERE id = ?8
    "#;

    debug!("the update sql: {}", sql);

    let affected_rows = conn
        .execute(
            sql,
            params![
                company.name,
                company.type_id,
                company.status,
                company.address,
                company.staff_num,
                company.remark,
                company.capacity,
                company.id,
            ],
        )
        .map_err(|e| {
            debug!("Update company failed: {}", e);
            MyError::database_error("Update company failed".to_string(), e.to_string())
        })?;

    if affected_rows == 0 {
        return Err(MyError::new(
            404,
            "Company not found".to_string(),
            "".to_string(),
        ));
    }

    Ok(Output::success(
        "Update successful".to_string(),
        Some(company),
    ))
}

#[tauri::command(rename_all = "snake_case")]
pub fn list_company(
    name: Option<String>,
    type_id: Option<i32>,
    status: Option<i32>,
    page_current: Option<i32>, // 当前页码，从1开始
    page_size: Option<i32>,    // 每页大小
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Pagination<Company>>, MyError> {
    debug!(
        "name: {:?}, type_id: {:?}, status: {:?}",
        name, type_id, status
    );
    // 设置分页参数默认值
    let page_current = page_current.unwrap_or(1).max(1); // 至少为1
    let page_size = page_size.unwrap_or(10).max(1).min(100); // 限制在1-100之间

    // 构建基础SQL查询（用于计数）
    let mut count_sql = "SELECT COUNT(*) FROM company WHERE 1=1".to_string();

    // 构建数据SQL查询
    let mut data_sql = r#"SELECT id, name, type_id, status, address, staff_num, capacity, remark,
                created_by, created_at, modified_by, modified_at
         FROM company
         WHERE 1=1"#
        .to_string();

    let mut params: Vec<&dyn ToSql> = Vec::new();

    // 使用局部变量存储参数值
    let name_param = name.as_ref().map(|name| format!("%{}%", name));

    // 添加筛选条件（同时应用到计数和数据查询）
    if let Some(ref name) = name_param {
        count_sql.push_str(" AND name LIKE ?");
        data_sql.push_str(" AND name LIKE ?");
        params.push(name);
    }

    if let Some(ref type_id) = type_id {
        count_sql.push_str(" AND type_id = ?");
        data_sql.push_str(" AND type_id = ?");
        params.push(type_id);
    }

    if let Some(ref status) = status {
        count_sql.push_str(" AND status = ?");
        data_sql.push_str(" AND status = ?");
        params.push(status);
    }

    debug!("count_sql: {}", count_sql);
    debug!("data_sql: {}", data_sql);

    // 添加排序和分页到数据查询
    data_sql.push_str(" ORDER BY id DESC");
    data_sql.push_str(" LIMIT ? OFFSET ?");

    let conn = state.conn.lock().unwrap();

    // 第一步：获取总记录数
    let total_count: i32 = {
        let mut stmt = conn.prepare(&count_sql).map_err(|e| {
            MyError::database_error(
                "Prepare count statement error in list_company".to_string(),
                e.to_string(),
            )
        })?;

        // 计算查询参数（只包含条件参数，不包含分页参数）
        let count_params: Vec<&dyn ToSql> = params.iter().map(|p| *p).collect();

        stmt.query_row(&*count_params, |row| row.get(0))
            .map_err(|e| {
                MyError::database_error(
                    "Query count error in list_company".to_string(),
                    e.to_string(),
                )
            })?
    };

    // 计算总页数
    let page_total = (total_count as f64 / page_size as f64).ceil() as i32;

    // 第二步：获取分页数据
    let mut data_params = params.clone();
    data_params.push(&page_size);
    let offset = (page_current - 1) * page_size;
    data_params.push(&offset);

    let mut stmt = conn.prepare(&data_sql).map_err(|e| {
        MyError::database_error(
            "Prepare data statement error in list_company".to_string(),
            e.to_string(),
        )
    })?;

    let rows = stmt
        .query_map(&*data_params, |row| {
            Ok(Company {
                id: row.get(0)?,
                name: row.get(1)?,
                type_id: row.get(2)?,
                status: row.get(3)?,
                address: row.get(4)?,
                contacts: Vec::new(),
                staff_num: row.get(5)?,
                capacity: row.get(6)?,
                remark: row.get(7)?,
                created_by: row.get(8)?,
                created_at: row.get(9)?,
                modified_by: row.get(10)?,
                modified_at: row.get(11)?,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query mapping error in list_company".to_string(),
                e.to_string(),
            )
        })?;

    let mut company_list = Vec::new();
    for row in rows {
        let company = row.map_err(|e| {
            MyError::database_error(
                "Row mapping error in list_company".to_string(),
                e.to_string(),
            )
        })?;
        company_list.push(company);
    }

    // 构建分页响应
    let pagination = Pagination {
        page_size,
        page_total,
        page_current,
        page_items: company_list,
    };

    debug!("company_list pagination: {:?}", pagination);
    Ok(Output::success("OK".to_string(), Some(pagination)))
}

#[tauri::command(rename_all = "snake_case")]
pub fn list_company_by_name(
    name: Option<String>,
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Vec<Company>>, MyError> {
    let (sql, params): (&str, Vec<&dyn ToSql>) = {
        (
            r#"SELECT id, name, type_id, status, address,  staff_num,capacity,remark,
                      created_by, created_at, modified_by, modified_at
               FROM company WHERE status = 1 AND name LIKE '%' || ?1 || '%'"#,
            vec![&name],
        )
    };

    let conn = state.conn.lock().unwrap();
    let mut stmt = conn.prepare(sql).map_err(|e| {
        MyError::database_error(
            "Prepare statement error in list_company".to_string(),
            e.to_string(),
        )
    })?;

    let rows = stmt
        .query_map(&*params, |row| {
            Ok(Company {
                id: row.get(0)?,
                name: row.get(1)?,
                type_id: row.get(2)?,
                status: row.get(3)?,
                address: row.get(4)?,
                contacts: Vec::new(),
                staff_num: row.get(5)?,
                capacity: row.get(6)?,
                remark: row.get(7)?,

                created_by: row.get(8)?,
                created_at: row.get(9)?,
                modified_by: row.get(10)?,
                modified_at: row.get(11)?,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query mapping error in list_company".to_string(),
                e.to_string(),
            )
        })?;

    let mut company_list = Vec::new();
    for row in rows {
        let company = row.map_err(|e| {
            MyError::database_error(
                "Row mapping error in list_company".to_string(),
                e.to_string(),
            )
        })?;
        company_list.push(company);
    }

    Ok(Output::success("OK".to_string(), Some(company_list)))
}

#[tauri::command(rename_all = "snake_case")]
pub fn company_detail(
    company_id: i32,
    state: State<'_, DatabaseConnection>,
) -> Result<Output<Company>, MyError> {
    let conn = state.conn.lock().unwrap();

    let sql = "SELECT id, name, type_id, status, address,  staff_num,capacity, remark,
              created_by, created_at, modified_by, modified_at
       FROM company WHERE id = ?";

    let mut stmt = conn.prepare(sql).map_err(|e| {
        MyError::database_error(
            "Prepare statement error in company_detail".to_string(),
            e.to_string(),
        )
    })?;

    let row = stmt
        .query_row(params![company_id], |row| {
            Ok(Company {
                id: row.get(0)?,
                name: row.get(1)?,
                type_id: row.get(2)?,
                status: row.get(3)?,
                address: row.get(4)?,
                staff_num: row.get(5)?,
                capacity: row.get(6)?,
                remark: row.get(7)?,
                contacts: Vec::new(),
                created_by: row.get(8)?,
                created_at: row.get(9)?,
                modified_by: row.get(10)?,
                modified_at: row.get(11)?,
            })
        })
        .map_err(|e| {
            MyError::database_error(
                "Query row error in company_detail".to_string(),
                e.to_string(),
            )
        })?;

    //todo!("query contacts ");

    Ok(Output::success("OK".to_string(), Some(row)))
}
